﻿using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Reflection;
using Dapper;
using Ly.EBussiness.Common.DapperUtils;
using Ly.EBussiness.Common.DapperUtils.Page;
using Microsoft.Extensions.Logging;
using MySqlConnector;
using Volo.Abp.DependencyInjection;
using Volo.Abp.EntityFrameworkCore;

namespace Ly.EBussiness.EntityFrameworkCore;

/// <summary>
/// 
/// </summary>
public class ReadOnlyQueryRepository : IReadOnlyQueryRepository, ISingletonDependency
{
    private static string DefaultSqlConnectionString =
        @"server=120.76.119.66;port=3306;user=root;pwd=q123456;database=EBussiness;";

    /// <summary>sql server 单次命令支持的最大参数数量 </summary>
    private const int ParamMaxSize = 2100;

    /// <summary>  </summary>
    private const string NoLOCK = " ";

    private readonly ILogger<ReadOnlyQueryRepository> _logger;

    /// <summary>
    /// 
    /// </summary>
    /// <param name="logger"></param>
    public ReadOnlyQueryRepository(ILogger<ReadOnlyQueryRepository> logger)
    {
        _logger = logger;
    }


    /// <summary>
    /// 分页查询，允许通过选项决定查读库还是写库
    /// </summary>
    /// <typeparam name="TOutPut"></typeparam>
    /// <typeparam name="TInPut"></typeparam>
    /// <param name="queryPageNormal"></param>
    /// <returns></returns>
    public async Task<PageResult<TOutPut>> GetAllPagedAsync<TOutPut, TInPut>(TInPut queryPageNormal)
        where TInPut : QueryPageBase
    {
        // 输出查询语句
        var pageTotalSql = queryPageNormal.BuildSelectPageTotalSql();
        // 获取总记录数
        var totalResult = await FirstOrDefaultAsync<PageResult<TOutPut>>(pageTotalSql, queryPageNormal);
        if (totalResult != null)
        {
            var selectSql = queryPageNormal.BuildSelectPageSql();
            IEnumerable<TOutPut> result = await QueryAsync<TOutPut>(selectSql, queryPageNormal); // 查询
            totalResult.Items = result.ToList();
        }

        return totalResult;
    }

    /// <summary>
    /// 不分页查询，全量查询
    /// </summary>
    /// <typeparam name="TInPut"></typeparam>
    /// <typeparam name="TOutPut"></typeparam>
    /// <param name="queryBase"></param>
    /// <returns></returns>
    public async Task<IList<TOutPut>> GetAllAsync<TOutPut, TInPut>(TInPut queryBase) where TInPut : QueryBase
    {
        // 获取总记录
        var selectSql = queryBase.BuildSelectSql();
        var result = await QueryAsync<TOutPut>(selectSql, queryBase); // 查询
        return result.ToList();
    }


    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public async Task<IList<T>> QueryAsync<T>(string sql, object param = null)
    {
        _logger.LogDebug(sql);
        using var executor = CreateExecutor();
        if (param is QueryBase queryBase) param = queryBase.GetWhereObject();
        var items = (await executor.QueryAsync<T>(sql, param)).ToList();
        return items;
    }


    /// <summary>
    /// 查询只读节点，异步节点数据
    /// 注意此处有sql参数总数不能超过2100个的限制，包含集合数量总参数不允许超过2100
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="param"></param>
    /// <returns></returns>
    public async Task<IList<T>> QueryAsync<T>(object param)
    {
        using var executor = CreateExecutor();
        var sql = BuildFirstSql<T>();
        var para = BuildParams(param);
        if (para.Value.Count > 0) sql += $" where {string.Join(" and ", para.Value)}";
        var items = (await executor.QueryAsync<T>(sql, para.Key))?.ToList() ?? new List<T>();
        return items;
    }


    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="param"></param>
    /// <returns></returns>
    public async Task<int> CountAsync<T>(object param)
    {
        using var executor = CreateExecutor();
        var sql = BuildCountSql<T>(param);
        return await executor.ExecuteScalarAsync<int>(sql, param);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public async Task<T> FirstOrDefaultAsync<T>(string sql, object param = null)
    {
        using var executor = CreateExecutor();
        if (param is QueryBase queryBase) param = queryBase.GetWhereObject();
        var item = await executor.QueryFirstOrDefaultAsync<T>(sql, param);
        return item;
    }


    /// <summary>
    /// 查询实体
    /// </summary>
    /// <param name="param"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public async Task<T> FirstOrDefaultAsync<T>(object param = null)
        where T : class, new()
    {
        using var dbConnection = CreateExecutor();
        var sql = BuildFirstSql<T>();
        var para = BuildParams(param);
        if (para.Value.Count > 0) sql += $" where {string.Join(" and ", para.Value)}";
        var item = await dbConnection.QueryFirstOrDefaultAsync<T>(sql, para.Key);
        return item;
    }

    private static IDbConnection CreateExecutor(string sqlConnectionString = null)
    {
        if (string.IsNullOrWhiteSpace(sqlConnectionString)) sqlConnectionString = DefaultSqlConnectionString;
        IDbConnection conn = new MySqlConnection(sqlConnectionString);
        conn.Open();
        return conn;
    }

    private static string BuildFirstSql<T>()
    {
        var tableName = GetTableName<T>();
        var fields = DapperHelp.GetFields<T>();
        var alias = fields.Select(p => $"{p.Key} {p.Value}");
        return $"select {string.Join(",", alias)} from {tableName}";
    }

    private static string GetTableName<T>()
    {
        return (typeof(T).GetCustomAttribute<TableAttribute>() ??
                throw new NotSupportedException("TableAttribute does not exist")).Name;
    }


    private static KeyValuePair<DynamicParameters, List<string>> BuildParams(object param)
    {
        var paramCount = 0;
        var re = new KeyValuePair<DynamicParameters, List<string>>(new DynamicParameters(), new List<string>());
        if (param == null) return re;
        var props = param.GetType().GetProperties();
        foreach (var prop in props)
        {
            if (ShouldIn(prop.PropertyType))
            {
                var isIn = prop.PropertyType.GetProperty("IsIn")?.GetValue(prop.GetValue(param));

                re.Key.Add(prop.Name, prop.GetValue(param));
                re.Value.Add($"{prop.Name} {((bool?)isIn ?? true ? "" : "not")} in @{prop.Name}");
                paramCount++;
                continue;
            }

            re.Value.Add($"{prop.Name}=@{prop.Name}");
            re.Key.Add(prop.Name, prop.GetValue(param));
            paramCount++;
        }

        if (paramCount > ParamMaxSize)
            throw new ArgumentException($"sqlServer单次查询只允许{ParamMaxSize}个参数,请手动拆分List类型参数数量，分批查询");
        return re;
    }


    private static bool ShouldIn(Type propertyType)
    {
        if (propertyType.IsGenericType)
        {
            return propertyType.GetGenericTypeDefinition() == typeof(List<>)
                   || propertyType.GetGenericTypeDefinition() == typeof(IEnumerable<>);
        }

        return false;
    }

    private static string BuildCountSql<T>(object param)
    {
        var tableName = GetTableName<T>();
        var para = BuildParams(param);
        var sql = $"select count(1) from {tableName}";
        if (para.Value.Count > 0) sql += $" where {string.Join(" and ", para.Value)}";
        return sql;
    }
}